﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace MISAPP.Classify.DataAccess
{
    public class DA_CFG002:DA_Matrix
    {
        protected string[] productList = { "車貸", "房貸", "信貸", "現金卡", "就貸" };
        protected string misbalString = string.Empty;

        public DA_CFG002()
        {
            tableName = "CFG001";
        }

        /// <summary>
        /// 
        /// </summary>
        public System.Data.DataTable GetSummaryData(string p_date,string[] p_dates,bool p_account)
        {
            ResetCriteria();
            string accString = p_account == true ? "=" : "<>";
            string sql = string.Format(
                    getSummaryDataStatement(),misbalString,accString
                    );
            FillFieldList(new string[] { p_date }, new string[] { "@YYYYMM" }, "YYYYMM");
            FillFieldList(p_dates, new string[] { "@起息日1", "@起息日2" }, "起息日");
            AddParameter(sql);
            return getData();
        }


        protected string getSummaryDataStatement()
        {
            /*
             * {0}→ 從前端取得使用者給的數字，產生成 UNION ALL SELECT '車貸',0, 1865023,0,'1'
             * {1}→ 會計科目要等於或是不等於
             */
            return @"
SELECT 
	產品名稱
	,CONVERT(VARCHAR, CAST(A.TOTAL AS MONEY ),1) AS TOTAL
	,CONVERT(VARCHAR, CAST(A.MISBAL AS MONEY ),1) AS MISBAL
	,CONVERT(VARCHAR, CAST(A.DIFF AS MONEY ),1) AS [差異]
	,OPT
FROM
(
	SELECT
		ISNULL(A.產品名稱,'合計') AS 產品名稱
		,MAX(A.TOTAL) AS TOTAL
		,MAX(A.MISBAL) AS MISBAL
		,MAX(A.TOTAL) - MAX(A.MISBAL) AS DIFF
		,A.OPT
	FROM 
	(
		SELECT
			 A.產品名稱
			,SUM(A.餘額) AS TOTAL
			,0 AS MISBAL
			,0 AS DIFF
			,'1' AS OPT
		FROM CFG001 A WITH(INDEX(IDX_CFG001_YM))
		WHERE A.YYYYMM =  @YYYYMM
		AND A.會計科目 {1} '1812'
		GROUP BY A.產品名稱
		
		UNION ALL		 
		
		SELECT
			A.產品名稱
			,SUM(A.餘額) AS TOTAL
			,0 AS MISBAL
			,0 AS DIFF
			,'2' AS OPT
		FROM CFG001 A WITH(INDEX(IDX_CFG001_YM))
		WHERE A.YYYYMM =  @YYYYMM
		AND (A.起息日 BETWEEN @起息日1 AND @起息日2)
		AND A.會計科目 <> '1812'
		GROUP BY A.產品名稱	
		
		{0}

	)A GROUP BY CUBE(A.產品名稱,A.OPT)
)A WHERE A.OPT IN ('1','2')	 
 ";
        }

        /// <summary>
        /// 產生出要在條件頁面上用來輸入數字
        /// </summary>
        /// <returns></returns>
        public System.Data.DataTable GetEmptyBALTable()
        {
            System.Data.DataTable dt = new System.Data.DataTable();
            dt.Columns.Add("產品名稱",typeof(System.String));
            dt.Columns.Add("MIS餘額", typeof(System.Int64));
            foreach (string col in productList)
            {
                System.Data.DataRow row = dt.NewRow();
                row[0] = col;
                dt.Rows.Add(row);
            }
            return dt;
        }

        public void CreateMISBALString(List<DataTable> p_bal)
        {
            string sql = " UNION ALL SELECT '{0}',0, {1},0,'{2}' \r\n";
            StringBuilder sb = new StringBuilder();
            int i = 1;
            foreach (DataTable dt in p_bal)
            {
                foreach (DataRow row in dt.Rows)
                {
                    sb.AppendFormat(sql, row[0].ToString(), row[1].ToString()
                        , i.ToString());
                }
                i++;
            }
            misbalString = sb.ToString();
        }
    }
}
